package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigInteger;
import java.util.List;

import static com.hualude.roadsign.service.impl.CountServiceImpl.convertParams;

/**
 * Created by ding on 2017/9/21.
 */
public class SignalMachineFaultCountRepositoryImpl {

    @PersistenceContext
    private EntityManager em;

    public List<String> getDealCounts(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.MachineMaintenanceTime from tb_machine_fault m,tb_user u where m.MachineMaintenanceStatus = 2 and " +
                "(m.MachineMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.MachineMaintainerId = u.userId and u.orgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getReportCounts(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select s.MachineTime from tb_machine_fault s where " +
                "(s.MachineTime between '" + year + "' and '" + yearNext + "') and  (s.MachineOrgId in (" + orgIds + ") or " +
                "s.MachineMainOrgId in (" + orgIds + "))";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<FaultCountDto> getExpenseCounts(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.MachineMaintenanceTime AS maintenanceTime,m.MachineMoney AS repairAmount from tb_machine_fault m,tb_user u where " +
                "(m.MachineMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.MachineMaintainerId = u.userId and u.orgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(FaultCountDto.class));
        List<FaultCountDto> maintenanceDtos = query.getResultList();
        return maintenanceDtos;
    }

    public List<SignalMachineFaultDto> getFault4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select f.MachineFaultId,f.SignalMachineId,f.MachinePhoto,p.SignalMachinePhoto,f.MachineLevel,f.MachineCheck,f.MachineMainCheck,\n" +
                "f.MachineTime,f.MachineCheckTime,o.OrgName AS machineOrgName,o2.orgName as machineMainOrgName,p.SignalMachinePeriod," +
                " p.SignalMachineStartRoad,p.SignalMachineEndRoad,p.SignalMachineType,p.signalMachineServiceLife,p.signalMachineIsAlarm,\n" +
                "p.SignalMachineArea,p.SignalMachineCity,p.SignalMachineProvince,u.UserName AS machineCreatorName\n" +
                "from tb_machine_fault f,tb_org o,tb_org o2,tb_signal_machine p,tb_user u\n" +
                "where f.MachineOrgId = o.OrgId and p.SignalMachineMainOrgId=o2.orgId and f.SignalMachineId = p.SignalMachineId  AND\n" +
                "f.MachineCreatorId = u.UserId";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and f.MachineTime between '" + dateStr[0] + "' and '" + dateStr[1] + 1 + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (f.MachineOrgId IN (" + orgIds + ") or " +
                    "f.MachineMainOrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY f.MachineTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineFaultDto.class));
        List<SignalMachineFaultDto> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public Integer getFaultCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select count(*) from tb_machine_fault f,tb_org o WHERE f.MachineOrgId= o.OrgId";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and f.MachineTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (f.MachineOrgId IN (" + orgIds + ") or " +
                    "f.MachineMainOrgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<SignalMachineFaultDto> getDevice4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "select m.MachineFaultId,m.MachinePhoto,m.MachineMaintenancePhoto,m.MachineMaintenanceStatus,m.MachineMaintenanceTime,machineMoney,\n" +
                "o.OrgName AS machineOrgName,m.SignalMachineId,s.SignalMachineEndRoad,s.SignalMachineStartRoad,s.SignalMachineArea,s.signalMachineServiceLife,\n" +
                "s.SignalMachineCity,s.SignalMachineProvince,o2.orgName as machineMainOrgName,s.SignalMachinePhoto,s.SignalMachinePeriod,u.UserName AS machineCreatorName\n" +
                "from tb_machine_fault m,tb_user u,tb_org o,tb_org o2,tb_signal_machine s \n" +
                "where m.MachineMaintenanceStatus=2 and m.MachineMaintainerId = u.userId and s.SignalMachineMainOrgId=o2.orgId\n" +
                "and u.OrgId = o.OrgId and m.SignalMachineId = s.SignalMachineId ";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and m.MachineMaintenanceTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (u.OrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY m.MachineMaintenanceTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineFaultDto.class));
        List<SignalMachineFaultDto> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public Integer getDeviceCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "select count(*) from tb_machine_fault m,tb_user u,tb_org o,tb_org o2,tb_signal_machine s \n" +
                "where m.MachineMaintenanceStatus=2 and m.MachineMaintainerId = u.userId and s.SignalMachineMainOrgId=o2.orgId\n" +
                "and u.OrgId = o.OrgId and m.SignalMachineId = s.SignalMachineId ";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and m.MachineMaintenanceTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (u.OrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY m.MachineMaintenanceTime DESC";
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public Integer getMachineCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select count(*) from tb_signal_machine where SignalMachineId like '%'";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:起始路段 4:终止路段 5:主管单位 6:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and SignalMachineProvince = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and SignalMachineCity = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and SignalMachineArea = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1") && sQuery[4].equals("-1")) {
            hql += " and (SignalMachineStartRoad ='" + sQuery[3] + "' or SignalMachineEndRoad ='" + sQuery[3] + "')";
        }
        if (sQuery[3].equals("-1") && !sQuery[4].equals("-1")) {
            hql += " and (SignalMachineEndRoad ='" + sQuery[4] + "' or SignalMachineStartRoad ='" + sQuery[4] + "')";
        }
        if (!sQuery[3].equals("-1") && !sQuery[4].equals("-1")) {
            hql += " and ((SignalMachineStartRoad ='" + sQuery[3] + "' and SignalMachineEndRoad ='" + sQuery[4] + "') or" +
                    "(SignalMachineStartRoad = '" + sQuery[4] + "' and SignalMachineEndRoad = '" + sQuery[3] + "'))";
        }
        if (!sQuery[5].equals("-1")) {
            hql += " and SignalMachineMainOrgId = '" + sQuery[5] + "'";
        }
        if (!sQuery[6].equals("-1")) {
            hql += " and SignalMachineOrgId = '" + sQuery[6] + "'";
        }
        if (actor != 1) {
            hql += " and (SignalMachineOrgId IN (" + orgIds + ") or " +
                    "SignalMachineMainOrgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<SignalMachineDto> getMachine4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select sm.SignalMachineId,sm.SignalMachinePhoto,sm.SignalMachineProvince,sm.SignalMachineCity," +
                "sm.SignalMachineArea,m.OrgName as signalMachineMainOrgName,o.OrgName as signalMachineOrgName,sm.SignalMachineLongitude," +
                "sm.SignalMachineLatitude,sm.SignalMachineType,sm.SignalMachineBrand,sm.SignalMachineIsAlarm," +
                "sm.SignalMachineStartRoad,sm.SignalMachineEndRoad,sm.SignalMachineInstallTime,sm.SignalMachineIsComplete" +
                " from tb_signal_machine sm " +
                "LEFT JOIN tb_org m ON sm.SignalMachineMainOrgId = m.OrgId " +
                "LEFT JOIN tb_org o ON sm.SignalMachineOrgId = o.OrgId " +
                "where sm.SignalMachineId LIKE '%'";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:起始路段 4:终止路段 5:主管单位 6:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and SignalMachineProvince = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and SignalMachineCity = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and SignalMachineArea = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1") && sQuery[4].equals("-1")) {
            hql += " and (SignalMachineStartRoad ='" + sQuery[3] + "' or SignalMachineEndRoad ='" + sQuery[3] + "')";
        }
        if (sQuery[3].equals("-1") && !sQuery[4].equals("-1")) {
            hql += " and (SignalMachineEndRoad ='" + sQuery[4] + "' or SignalMachineStartRoad ='" + sQuery[4] + "')";
        }
        if (!sQuery[3].equals("-1") && !sQuery[4].equals("-1")) {
            hql += " and ((SignalMachineStartRoad ='" + sQuery[3] + "' and SignalMachineEndRoad ='" + sQuery[4] + "') or" +
                    "(SignalMachineStartRoad = '" + sQuery[4] + "' and SignalMachineEndRoad = '" + sQuery[3] + "'))";
        }
        if (!sQuery[5].equals("-1")) {
            hql += " and SignalMachineMainOrgId = '" + sQuery[5] + "'";
        }
        if (!sQuery[6].equals("-1")) {
            hql += " and SignalMachineOrgId = '" + sQuery[6] + "'";
        }
        if (actor != 1) {
            hql += " and (SignalMachineOrgId IN (" + orgIds + ") or " +
                    "SignalMachineMainOrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY sm.SignalMachineInstallTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineDto.class));
        List<SignalMachineDto> signalMachineDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return signalMachineDtos;
    }

    public List<String> getProvinceName(String orgIds) throws Exception {
        String hql = "select distinct sm.SignalMachineProvince from tb_signal_machine sm where sm.SignalMachineOrgId in (" + orgIds + ") or " +
                "sm.SignalMachineMainOrgId in (" + orgIds + ")";
        hql += " order by convert(sm.SignalMachineProvince using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> province = query.getResultList();
        return province;
    }

    public List<String> getCityName(String orgIds, String provinceName) throws Exception {
        String hql = "select distinct sm.SignalMachineCity from tb_signal_machine sm where (sm.SignalMachineOrgId in (" + orgIds + ") or " +
                "sm.SignalMachineMainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and sm.SignalMachineProvince = '" + provinceName + "'";
        }
        hql += " order by convert(sm.SignalMachineCity using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> city = query.getResultList();
        return city;
    }

    public List<String> getAllAreaName(String orgIds, String provinceName, String cityName) throws Exception {
        String hql = "select distinct sm.SignalMachineArea from tb_signal_machine sm where (sm.SignalMachineOrgId IN (" + orgIds + ")" +
                " or sm.SignalMachineMainOrgId IN (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and sm.SignalMachineProvince ='" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and sm.SignalMachineCity ='" + cityName + "'";
        }
        hql += " order by convert(sm.SignalMachineArea using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> area = query.getResultList();
        return area;
    }


    public List<String> getStartRoadName(String orgIds, String provinceName, String cityName, String areaName,
                                         String endRoadName) throws Exception {
        String hql = "select distinct sm.SignalMachineStartRoad from tb_signal_machine sm where (sm.SignalMachineOrgId in (" + orgIds + ") or " +
                "sm.SignalMachineMainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and sm.SignalMachineProvince = '" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and sm.SignalMachineCity = '" + cityName + "'";
        }
        if (!areaName.equals("-1")) {
            hql += " and sm.SignalMachineArea = '" + areaName + "'";
        }
        if (!endRoadName.equals("-1")) {
            hql += " and sm.SignalMachineEndRoad = '" + endRoadName + "'";
        }
        hql += " order by convert(sm.SignalMachineStartRoad using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> road = query.getResultList();
        return road;
    }

    public List<String> getEndRoadName(String orgIds, String startRoad, String provinceName, String cityName,
                                       String areaName) throws Exception {
        String hql = "select distinct sm.SignalMachineEndRoad from tb_signal_machine sm where (sm.SignalMachineOrgId in (" + orgIds + ") or " +
                "sm.SignalMachineMainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and sm.SignalMachineProvince = '" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and sm.SignalMachineCity = '" + cityName + "'";
        }
        if (!areaName.equals("-1")) {
            hql += " and sm.SignalMachineArea = '" + areaName + "'";
        }
        if (!startRoad.equals("-1")) {
            hql += " and sm.SignalMachineStartRoad = '" + startRoad + "'";
        }
        hql += " order by convert(sm.SignalMachineEndRoad using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> road = query.getResultList();
        return road;
    }

    public List<Integer> getSignalMachineTypeCounts(String orgIds, String time, String province, String city, String area) throws Exception {
        String[] timeLimit = {"", ""};
        timeLimit = time.split(" - ");
        String hql = "select r.SignalMachineType from tb_signal_machine r  where (r.SignalMachineOrgId in (" + orgIds + ") or " +
                "r.SignalMachineMainOrgId in (" + orgIds + ")) AND (r.SignalMachineInstallTime between '" + timeLimit[0] + "' and '" + timeLimit[1] + "') " +
                " and r.SignalMachineProvince like '%" + province + "%' and r.SignalMachineCity like '%" + city + "%' " +
                " and r.SignalMachineArea like '%" + area + "%'    ORDER BY r.SignalMachineType";
        Query query = em.createNativeQuery(hql);
        List<Integer> counts = query.getResultList();
        return counts;
    }

    public List<SignalMachineDto> getMachineDtos4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "SELECT sm.SignalMachineCity,sm.SignalMachineProvince,sm.SignalMachineArea,o.OrgName AS signalMachineOrgName" +
                " FROM tb_signal_machine AS sm " +
                " INNER JOIN tb_org o ON sm.SignalMachineOrgId = o.OrgId where (sm.SignalMachineOrgId in (" + orgIds + ") or " +
                "sm.SignalMachineOrgId in (" + orgIds + "))";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and sm.SignalMachineProvince = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and sm.SignalMachineCity = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and sm.SignalMachineArea = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1")) {
            hql += " and sm.SignalMachineOrgId ='" + sQuery[3] + "'";
        }
        hql += " GROUP BY(sm.SignalMachineArea) ORDER BY convert(sm.SignalMachineArea using gbk) asc";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalMachineDto.class));
        List<SignalMachineDto> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public List<BigInteger> getMachineCounts(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "SELECT count(s.SignalMachineId) FROM " +
                "tb_signal_machine AS s " +
                " where  (s.SignalMachineOrgId in (" + orgIds + ") or " +
                "s.SignalMachineMainOrgId in (" + orgIds + "))";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and s.SignalMachineProvince = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and s.SignalMachineCity = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and s.SignalMachineArea = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1")) {
            hql += " and s.SignalMachineOrgId ='" + sQuery[3] + "'";
        }
        hql += " GROUP BY(s.SignalMachineArea) ORDER BY convert(s.SignalMachineArea using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<BigInteger> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }
}
